/**
 * 会员管理模型
 * @module models/membership
 */

const sqlite3 = require('sqlite3').verbose();
const config = require('../config/config');

/**
 * 会员管理类，提供会员相关的数据库操作
 */
class Membership {
  /**
   * 获取所有会员计划
   * @returns {Promise<Array>} 会员计划列表
   */
  static async getAllPlans() {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(config.dbPath);
      
      db.all('SELECT * FROM membership_plans ORDER BY price ASC', (err, rows) => {
        db.close();
        
        if (err) {
          return reject(err);
        }
        
        // 格式化结果
        const plans = rows.map(row => {
          return {
            id: row.id,
            name: row.name,
            price: row.price,
            currency: row.currency,
            billingCycle: row.period,
            features: JSON.parse(row.features),
            limits: JSON.parse(row.limits)
          };
        });
        
        resolve(plans);
      });
    });
  }
  
  /**
   * 根据ID获取会员计划
   * @param {string} planId - 会员计划ID
   * @returns {Promise<Object>} 会员计划
   */
  static async getPlanById(planId) {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(config.dbPath);
      
      db.get('SELECT * FROM membership_plans WHERE id = ?', [planId], (err, row) => {
        db.close();
        
        if (err) {
          return reject(err);
        }
        
        if (!row) {
          return resolve(null);
        }
        
        // 格式化结果
        const plan = {
          id: row.id,
          name: row.name,
          price: row.price,
          currency: row.currency,
          billingCycle: row.period,
          features: JSON.parse(row.features),
          limits: JSON.parse(row.limits)
        };
        
        resolve(plan);
      });
    });
  }
  
  /**
   * 插入或更新会员计划
   * @param {Object} planData - 会员计划数据
   * @returns {Promise<Object>} 插入/更新的会员计划
   */
  static async upsertPlan(planData) {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(config.dbPath);
      
      const { 
        id, 
        name, 
        price, 
        currency, 
        billingCycle, 
        features, 
        limits 
      } = planData;
      
      // 序列化JSON字段
      const featuresStr = JSON.stringify(features);
      const limitsStr = JSON.stringify(limits);
      
      // 检查计划是否存在
      db.get('SELECT id FROM membership_plans WHERE id = ?', [id], (err, row) => {
        if (err) {
          db.close();
          return reject(err);
        }
        
        if (row) {
          // 更新现有计划
          const stmt = db.prepare(`
            UPDATE membership_plans
            SET name = ?, price = ?, currency = ?, period = ?, features = ?, limits = ?
            WHERE id = ?
          `);
          
          stmt.run(
            name, 
            price, 
            currency, 
            billingCycle, 
            featuresStr, 
            limitsStr, 
            id, 
            function(err) {
              stmt.finalize();
              db.close();
              
              if (err) {
                return reject(err);
              }
              
              resolve({
                id,
                name,
                price,
                currency,
                billingCycle,
                features,
                limits
              });
            }
          );
        } else {
          // 创建新计划
          const stmt = db.prepare(`
            INSERT INTO membership_plans (id, name, price, currency, period, features, limits)
            VALUES (?, ?, ?, ?, ?, ?, ?)
          `);
          
          stmt.run(
            id, 
            name, 
            price, 
            currency, 
            billingCycle, 
            featuresStr, 
            limitsStr, 
            function(err) {
              stmt.finalize();
              db.close();
              
              if (err) {
                return reject(err);
              }
              
              resolve({
                id,
                name,
                price,
                currency,
                billingCycle,
                features,
                limits
              });
            }
          );
        }
      });
    });
  }
  
  /**
   * 获取用户会员状态
   * @param {number} userId - 用户ID
   * @returns {Promise<Object>} 用户会员状态
   */
  static async getUserMembershipStatus(userId) {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(config.dbPath);
      
      db.get(
        'SELECT id, username, is_pro, pro_expiry_date FROM users WHERE id = ?', 
        [userId], 
        (err, user) => {
          if (err) {
            db.close();
            return reject(err);
          }
          
          if (!user) {
            db.close();
            return resolve(null);
          }
          
          // 获取用户今日使用次数
          db.get(
            `SELECT COUNT(*) as count FROM usage_stats 
             WHERE user_id = ? AND date(used_at) = date('now')`,
            [userId],
            (err, usageToday) => {
              if (err) {
                db.close();
                return reject(err);
              }
              
              // 获取用户当月使用次数
              db.get(
                `SELECT COUNT(*) as count FROM usage_stats 
                 WHERE user_id = ? AND strftime('%Y-%m', used_at) = strftime('%Y-%m', 'now')`,
                [userId],
                (err, usageMonth) => {
                  db.close();
                  
                  if (err) {
                    return reject(err);
                  }
                  
                  // 确定当前计划
                  const isPro = user.is_pro === 1;
                  const proExpiryDate = user.pro_expiry_date;
                  const currentPlan = isPro ? 'pro' : 'free';
                  
                  // 计算剩余天数（如果是专业版）
                  let remainingDays = null;
                  if (isPro && proExpiryDate) {
                    const now = new Date();
                    const expiryDate = new Date(proExpiryDate);
                    const diffTime = expiryDate - now;
                    remainingDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
                    
                    // 如果已过期，则视为免费用户
                    if (remainingDays <= 0) {
                      remainingDays = 0;
                    }
                  }
                  
                  // 返回用户会员状态
                  resolve({
                    currentPlan,
                    isPro,
                    proExpiryDate,
                    usageStats: {
                      today: usageToday.count,
                      dailyLimit: isPro ? -1 : 10, // 免费用户每日限制10次
                      thisMonth: usageMonth.count
                    },
                    remainingDays
                  });
                }
              );
            }
          );
        }
      );
    });
  }
  
  /**
   * 更新用户会员状态
   * @param {number} userId - 用户ID
   * @param {Object} membershipData - 会员数据
   * @returns {Promise<Object>} 更新后的用户会员状态
   */
  static async updateUserMembership(userId, membershipData) {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(config.dbPath);
      
      const { isPro, proExpiryDate } = membershipData;
      
      db.run(
        'UPDATE users SET is_pro = ?, pro_expiry_date = ? WHERE id = ?',
        [isPro ? 1 : 0, proExpiryDate, userId],
        function(err) {
          if (err) {
            db.close();
            return reject(err);
          }
          
          if (this.changes === 0) {
            db.close();
            return reject(new Error('用户不存在'));
          }
          
          // 获取更新后的用户会员状态
          Membership.getUserMembershipStatus(userId)
            .then(status => {
              db.close();
              resolve(status);
            })
            .catch(err => {
              db.close();
              reject(err);
            });
        }
      );
    });
  }
  
  /**
   * 记录会员交易
   * @param {Object} transactionData - 交易数据
   * @returns {Promise<Object>} 交易记录
   */
  static async recordTransaction(transactionData) {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(config.dbPath);
      
      const { 
        userId, 
        planId, 
        amount, 
        currency, 
        status, 
        paymentMethod, 
        orderId 
      } = transactionData;
      
      const createdAt = new Date().toISOString();
      
      db.run(
        `INSERT INTO transactions 
         (user_id, plan_id, amount, currency, status, payment_method, order_id, created_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [userId, planId, amount, currency, status, paymentMethod, orderId, createdAt],
        function(err) {
          if (err) {
            db.close();
            return reject(err);
          }
          
          // 获取插入的交易记录
          db.get(
            'SELECT * FROM transactions WHERE id = ?',
            [this.lastID],
            (err, transaction) => {
              db.close();
              
              if (err) {
                return reject(err);
              }
              
              if (!transaction) {
                return reject(new Error('交易记录创建失败'));
              }
              
              // 获取会员计划名称
              Membership.getPlanById(planId)
                .then(plan => {
                  resolve({
                    id: transaction.id,
                    planId: transaction.plan_id,
                    planName: plan ? plan.name : '未知计划',
                    amount: transaction.amount,
                    currency: transaction.currency,
                    status: transaction.status,
                    paymentMethod: transaction.payment_method,
                    createdAt: transaction.created_at,
                    orderId: transaction.order_id
                  });
                })
                .catch(err => {
                  reject(err);
                });
            }
          );
        }
      );
    });
  }
  
  /**
   * 获取用户交易记录
   * @param {number} userId - 用户ID
   * @param {number} page - 页码，默认1
   * @param {number} limit - 每页记录数，默认10
   * @returns {Promise<Object>} 交易记录和分页信息
   */
  static async getUserTransactions(userId, page = 1, limit = 10) {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(config.dbPath);
      
      // 计算偏移量
      const offset = (page - 1) * limit;
      
      // 查询总记录数
      db.get(
        'SELECT COUNT(*) as count FROM transactions WHERE user_id = ?',
        [userId],
        (err, countResult) => {
          if (err) {
            db.close();
            return reject(err);
          }
          
          const total = countResult.count;
          const pages = Math.ceil(total / limit);
          
          // 查询交易记录
          db.all(
            `SELECT t.*, m.name as plan_name 
             FROM transactions t
             LEFT JOIN membership_plans m ON t.plan_id = m.id
             WHERE t.user_id = ?
             ORDER BY t.created_at DESC
             LIMIT ? OFFSET ?`,
            [userId, limit, offset],
            (err, rows) => {
              db.close();
              
              if (err) {
                return reject(err);
              }
              
              // 格式化结果
              const transactions = rows.map(row => {
                return {
                  id: row.id,
                  planId: row.plan_id,
                  planName: row.plan_name || '未知计划',
                  amount: row.amount,
                  currency: row.currency,
                  status: row.status,
                  paymentMethod: row.payment_method,
                  createdAt: row.created_at,
                  orderId: row.order_id
                };
              });
              
              // 返回交易记录和分页信息
              resolve({
                transactions,
                pagination: {
                  page,
                  limit,
                  total,
                  pages
                }
              });
            }
          );
        }
      );
    });
  }
}

module.exports = Membership; 